package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.common.Format;
import com.powerunion.datacollection.dao.AccessoriesDao;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.model.Accessories;
import com.powerunion.datacollection.model.SubmissionWorkItem;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.util.DBUtils;
import com.powerunion.util.io.CacheIOFactory;
import com.powerunion.util.log.LogHelper;

public class AccessoriesDaoImpl extends JdbcBase implements AccessoriesDao {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			AccessoriesDaoImpl.class);
	// 跟前台的状态对应，从数据库读取的附件信息都全为STATE_FINISHED状态
	private static final int STATE_FINISHED = 1;

	public boolean save(Accessories accessories, Connection conn)
			throws DaoException {
		logger.debug(Constants.INTOMETHOD + "save");

		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"insert into dc_accessories(id,file_content,file_name,submi_user,proc_id,act_id,")
				.append(
						"form_id,i_template,update_date,if_del) values(?,?,?,?,?,?,?,?,sysdate,0)");
		PreparedStatement ps = null;
		int effectCount = 0;
		try {
			ps = conn.prepareStatement(sql.toString());
			int i = 1;
			String id = String.valueOf(this.getSeq("dc_accessories"));
			ps.setString(i++, id);
			ps.setBinaryStream(i++, accessories.getFileContent(), accessories
					.getFileContent().available());
			ps.setString(i++, accessories.getFilename());
			ps.setString(i++, accessories.getSubmiUser());
			ps.setString(i++, accessories.getProcId());
			ps.setString(i++, accessories.getActId());
			ps.setString(i++, accessories.getFormId());
			ps.setInt(i++, accessories.getItemplate());

			effectCount = ps.executeUpdate();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("保存附件信息失败！");
		} finally {
			DBUtils.close(ps);
		}
		if (effectCount > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "附件信息"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger
						.debug("save:" + Constants.SAVE + "附件信息"
								+ Constants.FAIL);
			}
			return false;
		}
	}

	public boolean save(Accessories accessories) throws DaoException {
		logger.debug(Constants.INTOMETHOD + "save");

		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"insert into dc_accessories(id,file_content,file_name,submi_user,proc_id,act_id,")
				.append(
						"form_id,i_template,update_date,if_del) values(?,?,?,?,?,?,?,?,sysdate,0)");
		PreparedStatement ps = null;
		int effectCount = 0;
		Connection conn = null;
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			int i = 1;
			String id = String.valueOf(this.getSeq("dc_accessories"));
			ps.setString(i++, id);
			ps.setBinaryStream(i++, accessories.getFileContent(), accessories
					.getFileContent().available());
			ps.setString(i++, accessories.getFilename());
			ps.setString(i++, accessories.getSubmiUser());
			ps.setString(i++, accessories.getProcId());
			ps.setString(i++, accessories.getActId());
			ps.setString(i++, accessories.getFormId());
			ps.setInt(i++, accessories.getItemplate());

			effectCount = ps.executeUpdate();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("保存附件信息失败！");
		} finally {
			DBUtils.close(ps,conn);
		}
		if (effectCount > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "附件信息"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger
						.debug("save:" + Constants.SAVE + "附件信息"
								+ Constants.FAIL);
			}
			return false;
		}
	}

	public Accessories findAccessoriesByKey(String id) throws DaoException {
		logger.debug(Constants.INTOMETHOD + "findAccessoriesByKey");
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select a.id,a.file_content,a.file_name,a.file_type,b.full_name,a.submi_user,a.proc_id,a.act_id,a.form_id,a.update_date ")
				.append(
						" from dc_accessories a left join base_user b on a.submi_user = b.user_code where a.id=? ");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Accessories as = new Accessories();
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				int i = 1;
				as.setId(rs.getString(i++));
				as.setFileContent(CacheIOFactory.getInstance().copyToCache(
						rs.getBinaryStream(i++)));
				as.setFilename(rs.getString(i++));
				as.setFileType(rs.getString(i++));
				as.setSubmiUser(rs.getString(i++));
				as.setSubmiUserCode(rs.getString(i++));
				as.setProcId(rs.getString(i++));
				as.setActId(rs.getString(i++));
				as.setFormId(rs.getString(i++));
				Date upDate = rs.getDate(i++);
				String updateStr = Format.getInstance().dateToYMD(upDate);
				as.setUpdate(updateStr);

			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("查找ID为：" + id + " 的附件信息出现错误！");

		} finally {
			DBUtils.close(rs, ps, conn);
		}
		return as;
	}

	public List getAccessoriesByFormId(String formId) throws DaoException {
		logger.debug(Constants.INTOMETHOD + "getAccessoriesByFormId");

		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append(
						"select a.id,a.file_name,a.file_type,b.full_name,a.submi_user,a.proc_id,a.act_id,a.form_id,a.update_date from dc_accessories a")
				.append(
						" left join base_user b on a.submi_user = b.user_code where a.form_id=? and a.i_template = 0 and a.if_del <> 1 ");

		List list = null;

		QueryOperator query = new QueryOperator();
		query.setSql(sqlStr.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, formId));

		try {
			list = new ArrayList();
			query.access();
			if (query.resultSetAvailable()) {
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				for (int j = 0; j < rs.rowCount; j++) {
					int i = 0;
					Accessories as = new Accessories();
					as.setId(rs.getFieldValueAsString(i++));
					as.setFilename(rs.getFieldValueAsString(i++));
					as.setFileType(rs.getFieldValueAsString(i++));
					as.setSubmiUser(rs.getFieldValueAsString(i++));
					as.setSubmiUserCode(rs.getFieldValueAsString(i++));
					as.setProcId(rs.getFieldValueAsString(i++));
					as.setActId(rs.getFieldValueAsString(i++));
					as.setFormId(rs.getFieldValueAsString(i++));
					Date upDate = rs.getFieldValueAsDate(i++);
					String updateStr = Format.getInstance().dateToYMD(upDate);
					as.setUpdate(updateStr);
					as.setNote("读取成功");
					as.setState(STATE_FINISHED);

					list.add(as);
					rs.next();
				}

				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("查找表单编码为：" + formId + "的附件信息失败！", e);
			throw new DaoException("getAccessoriesByFormId throw error! ");
		}

		return list;
	}

	public final Accessories getAccessories(ResultSet rs) throws SQLException {
		Accessories ac = new Accessories();
		ac.setId(rs.getString("id"));
		ac.setFilename(rs.getString("file_name"));

		return ac;
	}

	public List getAccessoriesBySubmi(String formId, String submiUser,
			String procId, String actId) throws DaoException {
		logger.debug(Constants.INTOMETHOD + "getAccessoriesBySubmi");

		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append(
						"select a.id,a.file_name,a.file_type,b.full_name,a.submi_user,a.proc_id,a.act_id,a.form_id,a.update_date ")
				.append(
						" from dc_accessories a  left join base_user b on a.submi_user = b.user_code where a.form_id=? and a.i_template = 1 ")
				.append(
						" and a.submi_user=? and a.proc_id=? and a.act_id=? and a.if_del <> 1 ");

		List list = null;

		QueryOperator query = new QueryOperator();
		query.setSql(sqlStr.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
		query.addParameter(new SqlParameter(SqlType.VARCHAR, submiUser));
		query.addParameter(new SqlParameter(SqlType.VARCHAR, procId));
		query.addParameter(new SqlParameter(SqlType.VARCHAR, actId));

		try {
			list = new ArrayList();
			query.access();
			if (query.resultSetAvailable()) {
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				for (int j = 0; j < rs.rowCount; j++) {
					int i = 0;
					Accessories as = new Accessories();
					as.setId(rs.getFieldValueAsString(i++));
					as.setFilename(rs.getFieldValueAsString(i++));
					as.setFileType(rs.getFieldValueAsString(i++));
					as.setSubmiUser(rs.getFieldValueAsString(i++));
					as.setSubmiUserCode(rs.getFieldValueAsString(i++));
					as.setProcId(rs.getFieldValueAsString(i++));
					as.setActId(rs.getFieldValueAsString(i++));
					as.setFormId(rs.getFieldValueAsString(i++));
					Date upDate = rs.getFieldValueAsDate(i++);
					String updateStr = Format.getInstance().dateToYMD(upDate);
					as.setUpdate(updateStr);
					as.setNote("读取成功");
					as.setState(STATE_FINISHED);

					list.add(as);
					rs.next();
				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("查找上报人编码为：" + submiUser + "的附件信息失败！", e);
			throw new DaoException("getAccessoriesBySubmi throw error!");
		}
		return list;
	}

	// private boolean delete(String formId,Connection conn) throws DaoException
	// {
	// if (logger.isDebugEnabled()) {
	// logger.debug(Constants.INTOMETHOD + "delete");
	// }
	// String sql = "delete from dc_accessories where form_id = ? and i_template
	// = 0 ";
	// UpdateOperator update = new UpdateOperator();
	// update.setSql(sql);
	// update.setPresentConnection(conn);
	// update.setUseOnlyConnectionFlag(true);
	//		
	// update.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
	// try{
	// update.access();
	// }catch(Exception e){
	// logger.error("删除表单为ID:"+formId+" 初始化表单附件信息出错！", e);
	// throw new DaoException("删除表单为ID:"+formId+" 初始化表单附件信息出错！");
	// }
	//		
	// int effectCount = update.getEffectCounts();
	// if(effectCount >0) {
	// if (logger.isDebugEnabled()) {
	// logger.debug("delete:" + Constants.DELETE + "初始化表单附件信息出错"
	// + Constants.SUCCESS);
	// }
	// return true;
	// }else{
	// if (logger.isDebugEnabled()) {
	// logger.debug("delete:" + Constants.DELETE + "初始化表单附件信息出错"
	// + Constants.FAIL);
	// }
	// return false;
	// }
	// }

	private boolean delete(String formId, Connection conn) throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "delete");
		}
		String sql = "delete from dc_accessories where form_id = ?  and i_template = 0";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		update.setPresentConnection(conn);
		update.setUseOnlyConnectionFlag(true);

		update.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
		try {
			update.access();
		} catch (Exception e) {
			logger.error("删除表单为ID:" + formId + " 初始化表单附件信息出错！", e);
			throw new DaoException("删除表单为ID:" + formId + " 初始化表单附件信息出错！");
		}

		int effectCount = update.getEffectCounts();
		if (effectCount > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "初始化表单附件信息出错"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "初始化表单附件信息出错"
						+ Constants.FAIL);
			}
			return false;
		}
	}

	private boolean delete(String submiUser, String procId, String actId,
			String formId, Connection conn) throws DaoException {
		String sql = "delete from dc_accessories where submi_user=? and proc_id=? and form_id=? and act_id=? and i_template=1 ";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		update.setUseOnlyConnectionFlag(true);
		update.setPresentConnection(conn);
		update.addParameter(new SqlParameter(SqlType.VARCHAR, submiUser));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, procId));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, actId));
		try {
			update.access();
		} catch (Exception e) {
			logger.error("删除上报附件信息出错！", e);
			throw new DaoException("删除上报附件信息出错！");
		}

		int effectCount = update.getEffectCounts();
		if (effectCount > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "删除上报附件信息"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "删除上报附件信息"
						+ Constants.FAIL);
			}
			return false;
		}

	}

	public boolean delete(String[] codes) throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "delete");
		}
		String sql = "update dc_accessories set IF_DEL = 1 where id = ? ";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);

		for (int i = 0; i < codes.length; i++) {
			SqlParameterSet spSet = new SqlParameterSet();
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, codes[i]));
			update.addBatchParameter(spSet);
		}
		try {
			update.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("delete throw error!");
		}

		int[] effectCount = update.getBatchEffectCounts();
		if (effectCount != null && effectCount.length > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "附件信息"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "附件信息"
						+ Constants.FAIL);
			}
			return false;
		}

	}

	public List getAccessories4SubWork(String formId, String procId,
			String approveId) throws DaoException {
		logger.debug(Constants.INTOMETHOD + "getAccessories4SubmiId");

		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append(
						"select a.id,a.file_name,a.file_type,b.full_name,a.submi_user,a.proc_id,a.act_id,a.form_id,a.update_date ")
				.append(
						" from dc_accessories a  left join base_user b on a.submi_user = b.user_code where a.form_id=? and a.i_template = 1 ")
				.append(
						" and a.proc_id=?  and act_id in (select s.id from dc_submission_work s, dc_approve_work a ")
				.append(
						" where s.act_id = a.pre_act_id and a.id=? and a.if_del <> 1)");

		List list = null;

		QueryOperator query = new QueryOperator();
		query.setSql(sqlStr.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
		query.addParameter(new SqlParameter(SqlType.VARCHAR, procId));
		query.addParameter(new SqlParameter(SqlType.VARCHAR, approveId));

		try {
			list = new ArrayList();
			query.access();
			if (query.resultSetAvailable()) {
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				for (int j = 0; j < rs.rowCount; j++) {
					int i = 0;
					Accessories as = new Accessories();
					as.setId(rs.getFieldValueAsString(i++));
					as.setFilename(rs.getFieldValueAsString(i++));
					as.setFileType(rs.getFieldValueAsString(i++));
					as.setSubmiUser(rs.getFieldValueAsString(i++));
					as.setSubmiUserCode(rs.getFieldValueAsString(i++));
					as.setProcId(rs.getFieldValueAsString(i++));
					as.setActId(rs.getFieldValueAsString(i++));
					as.setFormId(rs.getFieldValueAsString(i++));
					Date upDate = rs.getFieldValueAsDate(i++);
					String updateStr = Format.getInstance().dateToYMD(upDate);
					as.setUpdate(updateStr);
					as.setNote("读取成功");

					list.add(as);
					rs.next();
				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("查找审批任务编码为：" + approveId + "的附件信息失败！", e);
			throw new DaoException("getAccessories4SubWork throw error!");
		}
		return list;
	}

	public List getAccessories4AllSubWork(String formId, String procId,
			List submissionWorkItems) throws DaoException {
		logger.debug(Constants.INTOMETHOD + "getAccessories4AllSubWork");

		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append(
						"select a.id,a.file_name,a.file_type,b.full_name,a.submi_user,a.proc_id,a.act_id,a.form_id,a.update_date ")
				.append(
						" from dc_accessories a  left join base_user b on a.submi_user = b.user_code where a.form_id=? and a.i_template = 1 ")
				.append(
						" and a.proc_id=?  and a.act_id = ? and a.if_del <> 1 and a.if_del <> 2 ");

		List list = null;

		Connection conn = null;
		try {
			list = new ArrayList();
			conn = this.getConnection();
			for (Iterator iterator = submissionWorkItems.iterator(); iterator
					.hasNext();) {
				SubmissionWorkItem swi = (SubmissionWorkItem) iterator.next();
				QueryOperator query = new QueryOperator();
				query.setUseOnlyConnectionFlag(true);
				query.setPresentConnection(conn);
				query.setSql(sqlStr.toString());
				query.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
				query.addParameter(new SqlParameter(SqlType.VARCHAR, procId));
				query.addParameter(new SqlParameter(SqlType.VARCHAR, swi
						.getId()));

				query.access();
				if (query.resultSetAvailable()) {
					// 获取并处理记录集
					RsDataSet rs = new RsDataSet(query.getSqlResultSet());
					for (int j = 0; j < rs.rowCount; j++) {
						int i = 0;
						Accessories as = new Accessories();
						as.setId(rs.getFieldValueAsString(i++));
						as.setFilename(rs.getFieldValueAsString(i++));
						as.setFileType(rs.getFieldValueAsString(i++));
						as.setSubmiUser(rs.getFieldValueAsString(i++));
						as.setSubmiUserCode(rs.getFieldValueAsString(i++));
						as.setProcId(rs.getFieldValueAsString(i++));
						as.setActId(rs.getFieldValueAsString(i++));
						as.setFormId(rs.getFieldValueAsString(i++));
						Date upDate = rs.getFieldValueAsDate(i++);
						String updateStr = Format.getInstance().dateToYMD(
								upDate);
						as.setUpdate(updateStr);
						as.setNote("读取成功");

						list.add(as);
						rs.next();
					}
					rs.clearAll();
				}
			}
		} catch (Exception e) {
			logger.error("查找上报人编码为：" + procId + "的附件信息失败！", e);
			throw new DaoException("getAccessories4AllSubWork throw error!");
		} finally {
			DBUtils.close(conn);
		}
		return list;
	}

}
